For Each State:
   For each major road in that state
      retreive it from the database
      if its an interstate, find its number and merge it with other segments for that interstate
      if its an us highway, find its number and merge it with other segments for that us highway
     if its an state highway, find its number and merge it with other segments for that state highway
     otherwise, add it to the "other" major roads of the same name.
   For each interstate, merge the segments, then generalise the result.
   For each us highway, merge the segments, then generalise the result.
   For each state highway, merge the segments, then generalise the result.
   For each "other" major road, merge the segments, then generalise the result.
   write the results to the database

-- create a table with just the major roads in it.
-- this will pick out A1x, A2x, A3x, P1x, P2x, P3x 
CREATE TABLE major_roads_tmp AS 
   SELECT * FROM roads WHERE cfcc_2 in (1,2,3);

create index altnames_indx on altname (module,tlid);
create index altnames_indx_module on altname (module);
create index altnames_indx_tlid on altname (tlid);
vacuum analyse altname;

create index majorroads_indx on major_roads_tmp (module,tlid);
create index majorroads_indx_module on major_roads_tmp (module);
create index majorroads_indx_tlid on major_roads_tmp (tlid);
vacuum analyse major_roads_tmp;




create table delme_major AS
  select major_roads_tmp.module,major_roads_tmp.tlid,the_geom,cfcc,cfcc_1,cfcc_2,cfcc_3,name,length_m,
          rtsq,feat
  FROM major_roads_tmp LEFT JOIN altname ON 
  (
      (major_roads_tmp.module = altname.module)
      AND 
      (major_roads_tmp.tlid = altname.tlid)
  );

insert into geometry_columns values ('','','delme_major','the_geom',2,1,'GEOMETRY'); 

create index delme_major_indx on delme_major (module,tlid);
create index delme_major_indx_module on delme_major (module);
create index delme_major_indx_tlid on delme_major (tlid);

ALTER TABLE delme_major ADD PRIMARY KEY (module,tlid);

vacuum analyse delme_major;

  
begin;
update delme_major set feat = feat||(select dm2.feat from delme_major dm2 where dm2.module=delme_major.module and delme_major.tlid = dm2.tlid and dm2.rtsq = 1)
  WHERE rtsq =2;
  
delete from delme_major 
  WHERE rtsq =1
  AND module||tlid in (select module||tlid from delme_major where rtsq=2);
commit;

update featureids set fedirp ='' where fedirp isnull;
 update featureids set fename ='' where fename isnull;
 update featureids set fetype ='' where fetype isnull;
 update featureids set fedirs ='' where fedirs isnull;
 
alter table featureids add full_name text;
update featureids set  full_name= trim( both ' ' from fedirp || ' ' || fename || ' ' || fetype || ' '|| fedirs) ;


alter table delme_major add column altname1 text;
alter table delme_major add column altname2 text;
alter table delme_major add column altname3 text;
alter table delme_major add column altname4 text;
alter table delme_major add column altname5 text;
alter table delme_major add column altname6 text;
alter table delme_major add column altname7 text;
alter table delme_major add column altname8 text;
alter table delme_major add column altname9 text;

update delme_major 
   set altname1 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[1])
   where array_upper(feat,1) >=1;

update delme_major 
   set altname2 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[2])
   where array_upper(feat,1) >=2;

update delme_major 
   set altname3 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[3])
   where array_upper(feat,1) >=3;

update delme_major 
   set altname4 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[4])
   where array_upper(feat,1) >=4;

update delme_major 
   set altname5 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[5])
   where array_upper(feat,1) >=5;

update delme_major 
   set altname6 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[6])
   where array_upper(feat,1) >=6;

update delme_major 
   set altname7 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[7])
   where array_upper(feat,1) >=7;

update delme_major 
   set altname8 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[8])
   where array_upper(feat,1) >=8;

update delme_major 
   set altname9 = 
     (select full_name from featureids where featureids.module = delme_major.module and featureids.feat= delme_major.feat[9])
   where array_upper(feat,1) >=9;

create table major_roads (
	state text, 
	gen_full geometry,
	gen_1 geometry,
	gen_2 geometry,
	gen_3 geometry,
	interstate int,
	ushighway  int,
	statehighway int,
	otherName text     ) with oids;

insert into geometry_columns values ('','','major_roads','gen_full',2,1,'GEOMETRY');
insert into geometry_columns values ('','','major_roads','gen_1',2,1,'GEOMETRY');
insert into geometry_columns values ('','','major_roads','gen_2',2,1,'GEOMETRY');
insert into geometry_columns values ('','','major_roads','gen_3',2,1,'GEOMETRY');

source go

update major_roads set gen_1 = simplify(gen_full,0.0002); -- 'high' resolution (1:100,000)
update major_roads set gen_2 = simplify(gen_full,0.002);  -- mid resolution (1:1,000,000)
update major_roads set gen_3 = simplify(gen_full,0.05);   -- low resolution (all usa)

CREATE INDEX "major_roads_spatial_ind" on major_roads using gist (gen_full gist_geometry_ops);
CREATE INDEX "major_roads_spatial_ind1" on major_roads using gist (gen_1 gist_geometry_ops);
CREATE INDEX "major_roads_spatial_ind2" on major_roads using gist (gen_2 gist_geometry_ops);
CREATE INDEX "major_roads_spatial_ind3" on major_roads using gist (gen_3 gist_geometry_ops);
vacuum analyse major_roads;